# -*- coding: utf-8 -*-
# @Time    : 2024/10/29 16:42
# @Author  : CHNJX
# @File    : excel_api.py
# @Desc    : 通过excel获取配置
import os

import pandas as pd
import json

# 加载Excel文件
from openpyxl import load_workbook

# debug
# EXCEL_PATH = f"{os.path.dirname(__file__)}/../../工作簿1.xlsx"
# product
EXCEL_PATH = f"/mnt/firmware_Submit_Test/log/自动化策略.xlsx"

def get_excel_data():
    df = pd.read_excel(EXCEL_PATH, sheet_name='Sheet1')

    # 去掉所有列为空的行
    df = df.dropna(how='all')

    # 初始化数据存储的字典
    output_data = {}
    current_model = None

    # 遍历DataFrame的每一行以构建输出字典
    for _, row in df.iterrows():
        # 如果‘型号试项’不为空，则视为新的型号
        if pd.notna(row['型号']):
            current_model = row['型号']
            output_data[current_model] = []

        # 如果‘用例覆盖’不为空且当前型号存在，处理测试项
        if pd.notna(row['用例覆盖']) and current_model:
            # 这里要判断row['用例覆盖']是否有含模块项
            modules = 'all'
            testsuite_name = row['用例覆盖']
            if '-' in row['用例覆盖']:
                testsuite_name, modules = row['用例覆盖'].split('-')
            testcase = {
                "TESTSUITE_NAME": testsuite_name,
                "MODULES": modules,
                "DESCRIPTION": "DESCRIPTION",
                "trigger_time": row['星期'] if row['星期'] != '全部' else "星期1,星期2,星期3,星期4,星期5,星期6,星期7"
            }
            output_data[current_model].append(testcase)
    return json.dumps(output_data, ensure_ascii=False, indent=4)


def update_run_count(device_name, idx, run_count):
    """
    根据设备名称和策略编号更新指定策略的运行次数到Excel表格，保持原有格式
    :param device_name: 设备名称
    :param idx: 策略编号
    :param run_count: 要更新的运行次数
    :param excel_path: Excel文件路径
    """
    print(f"设备名为：{device_name},策略编号为：{idx},运行次数为：{run_count}")
    workbook = load_workbook(EXCEL_PATH)
    sheet = workbook["Sheet1"]

    # 获取表头，找到“型号”和“上周总结”对应的列索引
    header_row = next(sheet.iter_rows(min_row=1, max_row=1, values_only=True))
    try:
        device_column = header_row.index("型号") + 1  # 获取“型号”列的索引
        summary_column = header_row.index("上周总结") + 1  # 获取“上周总结”列的索引
    except ValueError as e:
        raise ValueError(f"表头中缺少必要的列: {e}")

    # 查找设备名称所在的起始行
    target_row = None
    for row in sheet.iter_rows(min_row=2, values_only=False):
        if row[device_column - 1].value == device_name:  # 使用“型号”列的索引
            target_row = row[device_column - 1].row  # 获取设备名称所在的行号
            break

    # 如果找到设备名称行，则根据 idx 确定要更新的行
    if target_row is not None:
        update_row = target_row + idx  # 确定要更新的行号
        sheet.cell(row=update_row, column=summary_column).value = f"测试次数：{run_count}次"  # 更新“上周总结”列

    # 保存修改，但不改变原有格式
    print("-----------------保存修改-------------")
    workbook.save(EXCEL_PATH)
